import pymysql
from pymysql import escape_string
default_connect = {
    "host":'127.0.0.1',
    'username':"root",
    'password':'123456',
    "database":"crm_test",
}


default_connect['host'] = '192.168.2.181'
default_connect['password'] = 'MyNewPass4!'

class MysqlInstance(object):
    def __init__(self, connect_dict=default_connect):
        self.host = connect_dict['host']
        if 'user' in connect_dict:
            self.username = connect_dict['user']
        else:
            self.username = connect_dict['username']
        self.password = connect_dict['password']
        self.database = connect_dict['database']

        self.con = self.connect()
        self.cur = self.con.cursor()

    def connect(self):
        return pymysql.connect(
            host=self.host,
            user=self.username,
            password=self.password,
            database=self.database,
            charset='utf8',
            cursorclass=pymysql.cursors.DictCursor
        )

    def close(self):
        if self.cur: self.cur.close()
        if self.con: self.con.close()


    def search_info_from_mysql(self,table_name,conditions_dict,return_keys= []):
        '''
        根据condittion_dict里面的要求，从数据库相应的表名里面筛选出符合要求的数据
        :param table_name:
        :param conditions_dict:
        :param return_keys: select 查询返回的字段，如果没有，则 返回*
        :return:
        '''
        value_list = []
        return_keys_str = "*"
        if return_keys:
            return_keys_str = ""
            for key in return_keys:
                return_keys_str += "`%s`," % key
            return_keys_str = return_keys_str[:-1]
        base_search_str = 'select ' + return_keys_str +' from '+ table_name + " where "
        for key,value in conditions_dict.items():
            base_search_str += " %s=%s and" % (key,"%s")
            value_list.append(value)

        base_search_str = base_search_str[:-3]

        value_list = [escape_string(str(v)) for v in value_list]
        self.cur.execute(base_search_str,value_list)
        res = self.cur.fetchall()
        return res


    def insert_into_table(self,table_name,insert_dict):
        '''
        插入信息到相应的table里面去
        :param table_name:
        :param insert_dict:
        :return:
        '''

        base_search_str = 'insert into ' + table_name + " (%s)  values (%s)"
        key_string = ""
        value_string = ""
        value_list = []
        for key, value in insert_dict.items():
            key_string += "%s," % key
            value_string += "%s,"
            value_list.append(value)

        if key_string:
            key_string = key_string[:-1]
            value_stirng = value_string[:-1]
            base_search_str = base_search_str % (key_string,value_stirng)

            value_list = [escape_string(str(v)) for v in value_list]
            self.cur.execute(base_search_str,value_list)
            insert_id = self.con.insert_id()
            self.con.commit()
            return insert_id

    def update_into_table(self, table_name, conditions_dict,update_dict):
        '''
        跟新信息到相应的table里面去
        :param table_name:
        :param update_dict:
        :param conditions_dict
        :return:
        '''

        if update_dict:
            value_list = []
            base_search_str = 'update ' + table_name + " set "
            update_string = ""
            for key, value in update_dict.items():
                update_string += "`%s`" % key
                update_string += "=%s,"

                value_list.append(value)

            base_search_str += update_string[:-1]

            if conditions_dict:
                search_string = " where "
                for key, value in conditions_dict.items():
                    search_string += "%s=%s and" % (key, "%s")
                    value_list.append(value)
                base_search_str += search_string[:-3]

            value_list = [escape_string(str(v)) for v in value_list]
            self.cur.execute(base_search_str,value_list)
            self.con.commit()

    def delete_info_from_table(self, table_name, conditions_dict):
        """
        删除表的记录
        """
        value_list = []
        base_search_str = "DELETE FROM " + table_name
        search_string = " where "
        for key, value in conditions_dict.items():
            search_string += " %s=%s and" % (key, "%s")
            value_list.append(value)
        base_search_str += search_string[:-3]
        value_list = [escape_string(str(v)) for v in value_list]
        self.cur.execute(base_search_str, value_list)
        self.con.commit()


    def select_excute_sql(self,sql):
        self.cur.execute(sql)
        res = self.cur.fetchall()
        return res








